﻿//2013-12-20
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using PSS.Entity;
using PSS.Entity.SetManage.BasicData;
using PSS.Utility;

namespace PSS.DAL.SetManage.BasicData
{
    public class AccountDAL : DbHelperBase
    {
        public int GetTotal()
        {
            var strSql = new StringBuilder();
            strSql.Append(" select  count(*) AS [Total] FROM PSS_Account ");
            var count = 0;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["Total"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }
        public IList<AccountEntity> GetAccountList(Pagination qcmodel)
        {
            var list = new List<AccountEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            var start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            var end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by CreateTime) AS idx,AccountId,AccountCode,AccountName,BalanceDate,AccountBalance,AccountType,CreateTime FROM PSS_Account ");
            strSql.Append(" WHERE 1=1 ");
            strSql.Append(") AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new AccountEntity();
                            if (reader["AccountId"] != DBNull.Value)
                                model.AccountId = new Guid(reader["AccountId"].ToString());
                            model.AccountCode = reader["AccountCode"] == obj ? null : Convert.ToString(reader["AccountCode"]);
                            model.AccountName = reader["AccountName"] == obj ? null : Convert.ToString(reader["AccountName"]);
                            if (reader["BalanceDate"] != DBNull.Value)
                                model.BalanceDate = Convert.ToDateTime(reader["BalanceDate"].ToString());
                            if (reader["AccountBalance"] != DBNull.Value)
                                model.AccountBalance = Convert.ToDecimal(reader["AccountBalance"].ToString());
                            if (reader["AccountType"] != DBNull.Value)
                                model.AccountType = Convert.ToInt32(reader["AccountType"].ToString());
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
        public IList<AccountEntity> GetAccountListByCombobox()
        {
            var list = new List<AccountEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            strSql.Append(" SELECT AccountId,AccountCode,AccountName FROM PSS_Account Order by CreateTime  ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new AccountEntity();
                            if (reader["AccountId"] != DBNull.Value)
                                model.AccountId = new Guid(reader["AccountId"].ToString());
                            model.AccountCode = reader["AccountCode"] == obj ? null : Convert.ToString(reader["AccountCode"]);
                            model.AccountName = reader["AccountName"] == obj ? null : Convert.ToString(reader["AccountName"]);
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }
        public bool ModifyAccount(AccountEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE [PSS_Account] SET [AccountCode] = @AccountCode,[AccountName] = @AccountName,[BalanceDate] = @BalanceDate,[AccountBalance] = @AccountBalance,[AccountType] = @AccountType WHERE [AccountId] = @AccountId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@AccountCode", model.AccountCode);
                    Db.AddInParameter(cmd, "@AccountName", model.AccountName);
                    Db.AddInParameter(cmd, "@BalanceDate", model.BalanceDate);
                    Db.AddInParameter(cmd, "@AccountBalance", model.AccountBalance);
                    Db.AddInParameter(cmd, "@AccountType", model.AccountType);
                    Db.AddInParameter(cmd, "@AccountId", model.AccountId);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
        public bool AddAccount(AccountEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO [PSS_Account]([AccountId],[AccountCode],[AccountName],[BalanceDate],[AccountBalance],[AccountType]) ");
            strSql.Append(" VALUES(@AccountId,@AccountCode,@AccountName,@BalanceDate,@AccountBalance,@AccountType) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@AccountId", model.AccountId);
                    Db.AddInParameter(cmd, "@AccountCode", model.AccountCode);
                    Db.AddInParameter(cmd, "@AccountName", model.AccountName);
                    Db.AddInParameter(cmd, "@BalanceDate", model.BalanceDate);
                    Db.AddInParameter(cmd, "@AccountBalance", model.AccountBalance);
                    Db.AddInParameter(cmd, "@AccountType", model.AccountType);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
        public AccountEntity GetAccountInfo(string id)
        {
            var model = new AccountEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT [AccountId],[AccountCode],[AccountName],[BalanceDate],[AccountBalance],[AccountType] FROM [PSS_Account] WHERE AccountId=@AccountId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@AccountId", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            if (reader["AccountId"] != DBNull.Value)
                                model.AccountId = new Guid(reader["AccountId"].ToString());
                            model.AccountCode = reader["AccountCode"] == obj ? null : Convert.ToString(reader["AccountCode"]);
                            model.AccountName = reader["AccountName"] == obj ? null : Convert.ToString(reader["AccountName"]);
                            if (reader["BalanceDate"] != DBNull.Value)
                                model.BalanceDate = Convert.ToDateTime(reader["BalanceDate"].ToString());
                            if (reader["AccountBalance"] != DBNull.Value)
                                model.AccountBalance = Convert.ToDecimal(reader["AccountBalance"].ToString());
                            if (reader["AccountType"] != DBNull.Value)
                                model.AccountType = Convert.ToInt32(reader["AccountType"].ToString());
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }
        public bool RemoveAccount(string id)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" DELETE FROM [PSS_Account] WHERE AccountId=@AccountId ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@AccountId", id);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
